<?php
Class DossierDAO
{
	public $connexion;

	public function __construct()
	{
		try {
			$this->connexion = new PDO("mysql:host=".SERVEUR_MYSQL.";dbname=".BASE.";charset=utf8", UTILISATEUR, M2P);
		}
		catch(PDOException $e)
		{
			echo "Erreur : ".$e->getMessage();
			die();
		}
	}

	public function verifier_existance_dossier_by_numero($numero, $idcentre) {
		$query = "SELECT count(iddossier) FROM dossier WHERE numero  = :numero AND idcentre = :idcentre ";
		$requette_preparee = $this->connexion->prepare($query);
		$requette_preparee->bindValue(":numero" , trim($numero), PDO::PARAM_STR);
		$requette_preparee->bindValue(":idcentre" , trim($idcentre), PDO::PARAM_INT);
		$requette_preparee->execute();
		$nbDossier = $requette_preparee->fetchColumn();
		return ($nbDossier==0);
	}
	
	public function rechercher_idDossier_by_numero($numero, $idcentre) {
		$query = "SELECT iddossier FROM dossier WHERE numero  = :numero AND idcentre = :idcentre ";
		$requette_preparee = $this->connexion->prepare($query);
		$requette_preparee->bindValue(":numero" , trim($numero), PDO::PARAM_STR);
		$requette_preparee->bindValue(":idcentre" , trim($idcentre), PDO::PARAM_INT);
		$requette_preparee->execute();
		$idDossier = $requette_preparee->fetchColumn();
		return $idDossier;
	}
	
	public function rechercher_idPuce_by_numero($numero, $idcentre) {
		$query = "SELECT p.idpuce FROM dossier as d, puce as p WHERE d.iddossier = p.iddossier AND d.numero  = :numero AND d.idcentre = :idcentre ";
		$requette_preparee = $this->connexion->prepare($query);
		$requette_preparee->bindValue(":numero" , trim($numero), PDO::PARAM_STR);
		$requette_preparee->bindValue(":idcentre" , trim($idcentre), PDO::PARAM_INT);
		$requette_preparee->execute();
		$idPuce = $requette_preparee->fetchColumn();
		return $idPuce;
	}
	public function rechercher_numero_by_idpuce($idpuce) {
		$query = "SELECT d.numero FROM dossier as d, puce as p WHERE d.iddossier = p.iddossier AND p.idpuce  = :idpuce ";
		$requette_preparee = $this->connexion->prepare($query);
		$requette_preparee->bindValue(":idpuce" , $idpuce, PDO::PARAM_INT);
		$requette_preparee->execute();
		$numero = $requette_preparee->fetchColumn();
		return $numero;
	}
	public function rechercher_id_dossier($parPhenotypePersonne,$categorie,$parCentre)
	{
		$query = "SELECT distinct d.iddossier "
		."FROM dossier as d ";
		$query .= " LEFT OUTER JOIN categorie_biomed as cb ON cb.idcategorie = d.idcategoriebiomed ";
		$query .= " WHERE 1 ";
		if ($categorie != null && count($categorie) > 0){
			$queryTypeCat = "";
			$queryTypeCat1 = "";
			if (isset ($categorie['prenatal']) && $categorie['prenatal'] == 0 ){
				$queryTypeCat .= "1,";
			}
			if (isset ($categorie['postnatal']) && $categorie['postnatal'] == 0 ){
				$queryTypeCat .= "2,";
			}
			if (isset ($categorie['foetopat']) && $categorie['foetopat'] == 0 ){
				$queryTypeCat .= "3,";
			}
			if ($queryTypeCat != ""){
				$queryTypeCat1 = " AND (cb.idtypecategorie in (".substr($queryTypeCat, 0 , -1 ).")";
			} 
			$queryCat = "";
			if (isset ($categorie['prenatal']) && $categorie['prenatal'] != 0 ){
				$queryCat .= $categorie['prenatal'].",";
			}
			if (isset ($categorie['postnatal']) && $categorie['postnatal'] != 0 ){
				$queryCat .= $categorie['postnatal'].",";
			}
			if (isset ($categorie['foetopat']) && $categorie['foetopat'] != 0 ){
				$queryCat .= $categorie['foetopat'].",";
			}
			if ($queryCat != ""){
				if ($queryTypeCat1 != ""){
					$query .= $queryTypeCat1." OR cb.idcategorie in (".substr($queryCat, 0 , -1 )."))";
				} else  {
					$query .= " AND cb.idcategorie in (".substr($queryCat, 0 , -1 ).")";
				}	
			} else {
				$query .=  $queryTypeCat1.")";
			}
		}
		if ($parCentre != null && $parCentre != 0){
			$query .= " AND idcentre = :idcentre";
		}

		$requette_preparee = $this->connexion->prepare($query);
		if ($parCentre != null && $parCentre != 0){
			$requette_preparee->bindValue(":idcentre" , $parCentre, PDO::PARAM_INT);
		}
		
		$requette_preparee->execute();
		$iddossiers = $requette_preparee->fetchAll(PDO::FETCH_COLUMN, 0);
		

		if ($parPhenotypePersonne != null){
			$tabPhenotypes = split(";",$parPhenotypePersonne);
			$iddossiersFinal = array();
			$i = 0;
			foreach ($iddossiers as $iddossier){
					$query2 = "SELECT count(f.libelle) FROM phenotype_dossier fd , phenotype f WHERE f.idphenotype = fd.idPhenotype AND fd.iddossier = :iddossier AND (";
					foreach ($tabPhenotypes as $tabPhenotype){
						$query2 .= "f.libelle LIKE '".$tabPhenotype."' OR ";
					}
					$query2 = substr($query2,0,-3).")";

					
 					$requette_preparee2 = $this->connexion->prepare($query2);
 					$requette_preparee2->bindValue(":iddossier" , $iddossier, PDO::PARAM_INT);
 					$requette_preparee2->execute();
					$nbpheno = $requette_preparee2->fetchColumn();
					if ($tabPhenotypes[0]=="et"){
						if ($nbpheno== count($tabPhenotypes)-1){
							$iddossiersFinal[$i] = $iddossier;
							$i++;
						}
					} else if ($tabPhenotypes[0]=="ou"){
						if ($nbpheno>0){
							$iddossiersFinal[$i] = $iddossier;
							$i++;
						}
					}
			}
			$iddossiers = $iddossiersFinal;
		}	
		return  $iddossiers;

	} // rechercher_dossier

	public function recupere_dossier_par_id_pour_tableau_recherche($iddossier)
	{
		$query = "SELECT d.iddossier, d.numero, d.sexe, cat.libelle as categorie, d.idMedecin,  d.atteint, prel.libelle as typeprelevement, mp.libelle as marquepuce, tp.libelle as typepuce , count(cnv.idcnv) as nbcnv, p.formulechromosomique, c.nom as centre, d.autrephenotype "
		."FROM dossier as d left outer join puce as p on d.iddossier = p.iddossier ";
		$query .= " left outer join cnv as cnv on cnv.idpuce = p.idpuce ";
		$query .= " left outer join type_puce as tp  on tp.idtypepuce = p.idtypepuce ";
		$query .= " left outer join marque_puce as mp  on mp.idmarquepuce = tp.idmarquepuce ";
		$query .= " left outer join  type_prelevement as prel  on prel.idtypeprelevement = p.idtypeprelevement ";
		$query .= " left outer join  centre as c  on d.idcentre = c.idcentre ";
		$query .= " left outer join  categorie_biomed as cat  on cat.idcategorie = d.idcategoriebiomed  ";
		$query .=" WHERE 1 ";

		if ($iddossier != null && $iddossier != 0){
			$query .= " AND d.iddossier = :iddossier";
		}
		$requette_preparee = $this->connexion->prepare($query);
		if ($iddossier != null){
			$requette_preparee->bindValue(":iddossier" , $iddossier, PDO::PARAM_INT);
		}
		$requette_preparee->execute();
		$dossiers = $requette_preparee->fetchAll();
		return  $dossiers;

	} // rechercher_dossier
	
	public function rechercher_dossier_pour_formulaire($iddossier)
	{
		$query = "SELECT d.* "
				."FROM dossier as d ";
		$query .=" WHERE 1 ";
		if ($iddossier != null && $iddossier != 0){
			$query .= " AND d.iddossier = :iddossier";
		}
		$requette_preparee = $this->connexion->prepare($query);
		if ($iddossier != null){
			$requette_preparee->bindValue(":iddossier" , $iddossier, PDO::PARAM_INT);
		}
		$requette_preparee->execute();
		$dossier = $requette_preparee->fetchObject('Dossier');
		
		return  $dossier;
	
	} // rechercher_dossier_pour_formulaire

// 	public function rechercher_numero_dossier($iddossier)
// 	{
// 		$query = "SELECT d.numero "
// 				."FROM dossier as d ";
// 		$query .=" WHERE 1 ";
// 		if ($iddossier != null && $iddossier != 0){
// 			$query .= " AND d.iddossier = :iddossier";
// 		}
// 		$requette_preparee = $this->connexion->prepare($query);
// 		if ($iddossier != null){
// 			$requette_preparee->bindValue(":iddossier" , $iddossier, PDO::PARAM_INT);
// 		}
// 		$requette_preparee->execute();
// 		$numero = $requette_preparee->fetchColumn();
	
// 		return  $numero;
	
// 	} // rechercher_dossier
	
// 	public function rechercher_numero_dossier_by_idPuce($idpuce)
// 	{
// 		$query = "SELECT d.numero "
// 				."FROM dossier as d, puce as p ";
// 		$query .=" WHERE d.iddossier = p.iddossier ";
// 		if ($idpuce != null && $idpuce != 0){
// 			$query .= " AND p.idpuce = :idpuce";
// 		}
// 		$requette_preparee = $this->connexion->prepare($query);
// 		if ($idpuce != null){
// 			$requette_preparee->bindValue(":idpuce" , $idpuce, PDO::PARAM_INT);
// 		}
// 		$requette_preparee->execute();
// 		$numero = $requette_preparee->fetchColumn();
	
// 		return  $numero;
	
// 	} // rechercher_dossier
	
	
public function ajouter_dossier($dossier)
	{
		$message = null;
 		$query = "INSERT INTO dossier ( idcentre,  	idcategoriebiomed, numero, sexe, idmedecin, foetus, atteint, autrephenotype ) values ";
 		$query .= " ( :idcentre, :idcategoriebiomed, :numero, :sexe, :idmedecin, :foetus, :atteint, :autrephenotype)";
		$requette_preparee = $this->connexion->prepare($query);
 		if ($dossier->idcentre == null){
 			$requette_preparee->bindValue(":idcentre" , 0, PDO::PARAM_INT);
 		} else {
 			$requette_preparee->bindValue(":idcentre" , $dossier->idcentre, PDO::PARAM_INT);
 		}
 		if ($dossier->idcategoriebiomed == null){
 			$requette_preparee->bindValue(":idcategoriebiomed" , NULL, PDO::PARAM_NULL);
 		} else {
 			$requette_preparee->bindValue(":idcategoriebiomed" , $dossier->idcategoriebiomed, PDO::PARAM_INT);
 		}
 		if ($dossier->numero == null){
 			$requette_preparee->bindValue(":numero" , NULL, PDO::PARAM_NULL); 
 		} else {
 			$requette_preparee->bindValue(":numero" , $dossier->numero, PDO::PARAM_STR);
 		}
 		if ($dossier->sexe == null){
 			$requette_preparee->bindValue(":sexe" , "I", PDO::PARAM_STR);
 		} else {
 			$requette_preparee->bindValue(":sexe" , $dossier->sexe, PDO::PARAM_STR);
 		};
 		if ($dossier->idmedecin == null){
 			$requette_preparee->bindValue(":idmedecin" , 0, PDO::PARAM_INT);
 		} else {
 			$requette_preparee->bindValue(":idmedecin" , $dossier->idmedecin, PDO::PARAM_INT);
 		};
 		if ($dossier->foetus == null){
 			$requette_preparee->bindValue(":foetus" , 0, PDO::PARAM_INT);
 		} else {
 			$requette_preparee->bindValue(":foetus" , $dossier->foetus, PDO::PARAM_INT);
 		}
 		if ($dossier->atteint == null){
 			$requette_preparee->bindValue(":atteint" , 1, PDO::PARAM_INT);
 		} else {
 			$requette_preparee->bindValue(":atteint" , $dossier->atteint, PDO::PARAM_INT);
 		}
 		if ($dossier->autrephenotype == null){
 			$requette_preparee->bindValue(":autrephenotype" , NULL, PDO::PARAM_NULL);
 		} else {
 			$requette_preparee->bindValue(":autrephenotype" , $dossier->autrephenotype, PDO::PARAM_STR);
 		}
  	  	$t = $requette_preparee->execute();
  	  	
  	  	if(!$t)
 		{
 			$taberr = $requette_preparee->errorInfo();
 			$message = "Erreur numéro :".$taberr[0]."(".$taberr[1].")";
 			
  		} else  {
  			$message = $this->connexion->lastInsertId();
  		}
		return $message;
	}//ajouter_DOSSIER
	
	public function modifier_dossier($dossier)
	{
		$message = null;
		$query = "UPDATE dossier SET ";
		if ($dossier->idcentre != null){
			$query.= "idcentre = :idcentre , ";
		}
		if ($dossier->idcategoriebiomed != null){
			$query.= "idcategoriebiomed = :idcategoriebiomed , ";
		}
		if ($dossier->numero != null){
			$query.= "numero = :numero , ";
		}
		if ($dossier->sexe != null){
			$query.= "sexe = :sexe , ";
		}
		if ($dossier->idmedecin != null){
			$query.= "idmedecin = :idmedecin , ";
		}
		if ($dossier->foetus != null){
			$query.= "foetus = :foetus , ";
		}
		if ($dossier->atteint != null){
			$query.= "atteint = :atteint , ";
		}
		if ($dossier->autrephenotype != null){
			$query.= "autrephenotype = :autrephenotype , ";
		}
		$query = substr($query, 0,-2);
		$query = $query." WHERE iddossier= :iddossier";
		$requette_preparee = $this->connexion->prepare($query);
		if ($dossier->idcentre != null){
		$requette_preparee->bindValue(":idcentre" , $dossier->idcentre, PDO::PARAM_INT);
		}
		if ($dossier->idcategoriebiomed != null){
		$requette_preparee->bindValue(":idcategoriebiomed" , $dossier->idcategoriebiomed, PDO::PARAM_INT);
		}
		if ($dossier->numero != null){
		$requette_preparee->bindValue(":numero" , $dossier->numero, PDO::PARAM_STR);
		}
		if ($dossier->sexe != null){
			$requette_preparee->bindValue(":sexe" , $dossier->sexe, PDO::PARAM_STR);
		}
		if ($dossier->idmedecin != null){
			$requette_preparee->bindValue(":idmedecin" , $dossier->idmedecin, PDO::PARAM_STR);
		};
		if ($dossier->foetus != null){
			$requette_preparee->bindValue(":foetus" , $dossier->foetus, PDO::PARAM_STR);
		};
		if ($dossier->atteint != null){
			$requette_preparee->bindValue(":atteint" , $dossier->atteint, PDO::PARAM_INT);
		}
		if ($dossier->autrephenotype != null){
			$requette_preparee->bindValue(":autrephenotype" , $dossier->autrephenotype, PDO::PARAM_INT);
		}	
		
		$requette_preparee->bindValue(":iddossier" , $dossier->iddossier, PDO::PARAM_INT);
		$t = $requette_preparee->execute();
	
		if(!$t)
		{
			
			$taberr = $requette_preparee->errorInfo();
			$message = "Erreur numéro :".$taberr[0]."(".$taberr[1].")";
	
		} else {
			$message = $dossier->iddossier;
		}
		return $message;
	}
	

} // DossierDAO



?>
